{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 引擎"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite+pysqlite:///:memery:', echo=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,286 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,287 INFO sqlalchemy.engine.Engine select \"hello word\"\n",
      "2024-08-29 08:52:26,288 INFO sqlalchemy.engine.Engine [generated in 0.00164s] ()\n",
      "[('hello word',)]\n",
      "2024-08-29 08:52:26,289 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import text \n",
    "\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(text('select \"hello word\"'))\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,297 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,298 INFO sqlalchemy.engine.Engine create table some_table (x int, y int)\n",
      "2024-08-29 08:52:26,299 INFO sqlalchemy.engine.Engine [generated in 0.00220s] ()\n",
      "2024-08-29 08:52:26,305 INFO sqlalchemy.engine.Engine insert into some_table (x, y) values (?, ?)\n",
      "2024-08-29 08:52:26,306 INFO sqlalchemy.engine.Engine [generated in 0.00085s] [(1, 1), (2, 4)]\n",
      "2024-08-29 08:52:26,307 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    conn.execute(text(\"create table some_table (x int, y int)\"))\n",
    "    conn.execute(\n",
    "        text(\"insert into some_table (x, y) values (:x, :y)\"),\n",
    "        [\n",
    "            {\"x\": 1, \"y\": 1},\n",
    "            {\"x\": 2, \"y\": 4},\n",
    "        ]\n",
    "    )\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,318 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,319 INFO sqlalchemy.engine.Engine insert into some_table (x, y) values (?, ?)\n",
      "2024-08-29 08:52:26,319 INFO sqlalchemy.engine.Engine [cached since 0.01451s ago] [(6, 8), (9, 10)]\n",
      "2024-08-29 08:52:26,320 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    conn.execute(\n",
    "        text(\"insert into some_table (x, y) values (:x, :y)\"),\n",
    "        [\n",
    "            {\"x\": 6, \"y\": 8},\n",
    "            {\"x\": 9, \"y\": 10},\n",
    "        ]\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Result 对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,336 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,338 INFO sqlalchemy.engine.Engine select x, y from some_table\n",
      "2024-08-29 08:52:26,339 INFO sqlalchemy.engine.Engine [generated in 0.00077s] ()\n",
      "x: 1, y: 1\n",
      "x: 2, y: 4\n",
      "x: 6, y: 8\n",
      "x: 9, y: 10\n",
      "2024-08-29 08:52:26,340 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table\")\n",
    "    )\n",
    "\n",
    "    for row in result:\n",
    "        print(f'x: {row.x}, y: {row.y}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 元组赋值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,348 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,349 INFO sqlalchemy.engine.Engine select x, y from some_table\n",
      "2024-08-29 08:52:26,350 INFO sqlalchemy.engine.Engine [cached since 0.01126s ago] ()\n",
      "1 1\n",
      "2 4\n",
      "6 8\n",
      "9 10\n",
      "2024-08-29 08:52:26,351 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table\")\n",
    "    )\n",
    "    for x, y in result:\n",
    "        print(x, y) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数字索引\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,357 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,358 INFO sqlalchemy.engine.Engine select x, y from some_table\n",
      "2024-08-29 08:52:26,358 INFO sqlalchemy.engine.Engine [cached since 0.01983s ago] ()\n",
      "1 1\n",
      "2 4\n",
      "6 8\n",
      "9 10\n",
      "2024-08-29 08:52:26,359 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "\n",
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table\")\n",
    "    )\n",
    "    for row in result:\n",
    "        x = row[0]\n",
    "        y = row[1]\n",
    "        print(x, y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 属性名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,365 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,367 INFO sqlalchemy.engine.Engine select x, y from some_table\n",
      "2024-08-29 08:52:26,367 INFO sqlalchemy.engine.Engine [cached since 0.02861s ago] ()\n",
      "1 1\n",
      "2 4\n",
      "6 8\n",
      "9 10\n",
      "2024-08-29 08:52:26,368 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table\")\n",
    "    )\n",
    "    \n",
    "    for row in result:\n",
    "        x = row.x\n",
    "        y = row.y\n",
    "        print(x, y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 映射访问"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,374 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,375 INFO sqlalchemy.engine.Engine select x, y from some_table\n",
      "2024-08-29 08:52:26,376 INFO sqlalchemy.engine.Engine [cached since 0.03726s ago] ()\n",
      "1 1\n",
      "2 4\n",
      "6 8\n",
      "9 10\n",
      "2024-08-29 08:52:26,377 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table\")\n",
    "    )\n",
    "\n",
    "    for dict_row in result.mappings():\n",
    "        x = dict_row['x']\n",
    "        y = dict_row['y']\n",
    "        print(x, y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 执行参数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,381 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,382 INFO sqlalchemy.engine.Engine select x, y from some_table where y > ?\n",
      "2024-08-29 08:52:26,383 INFO sqlalchemy.engine.Engine [generated in 0.00042s] (9,)\n",
      "9 10\n",
      "2024-08-29 08:52:26,384 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    result = conn.execute(\n",
    "        text(\"select x, y from some_table where y > :y\"),\n",
    "        {\"y\": 9}\n",
    "    )\n",
    "    for x, y in result:\n",
    "        print(x, y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,390 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,391 INFO sqlalchemy.engine.Engine insert into some_table (x, y) values(?, ?)\n",
      "2024-08-29 08:52:26,392 INFO sqlalchemy.engine.Engine [generated in 0.00050s] [(11, 12), (13, 14)]\n",
      "2024-08-29 08:52:26,393 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    conn.execute(\n",
    "        text(\"insert into some_table (x, y) values(:x, :y)\"),\n",
    "        [ \n",
    "            {\"x\": 11, \"y\": 12},\n",
    "            {\"x\": 13, \"y\": 14},\n",
    "        ]\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 会话"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,465 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,466 INFO sqlalchemy.engine.Engine select x, y from some_table where y > ? order by x, y\n",
      "2024-08-29 08:52:26,466 INFO sqlalchemy.engine.Engine [generated in 0.00044s] (6,)\n",
      "6 8\n",
      "9 10\n",
      "11 12\n",
      "13 14\n",
      "2024-08-29 08:52:26,467 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm import Session \n",
    "\n",
    "stmt = text(\"select x, y from some_table where y > :y order by x, y\")\n",
    "\n",
    "with Session(engine) as session:\n",
    "    with session.begin():\n",
    "        result = session.execute(stmt, {\"y\": 6})\n",
    "        for x, y in result:\n",
    "            print(x, y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:26,473 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:26,474 INFO sqlalchemy.engine.Engine update some_table set y = ? where x = ?\n",
      "2024-08-29 08:52:26,474 INFO sqlalchemy.engine.Engine [generated in 0.00039s] [(11, 9), (15, 13)]\n",
      "2024-08-29 08:52:26,475 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with Session(engine) as session:\n",
    "    with session.begin():\n",
    "        result = session.execute(\n",
    "            text(\"update some_table set y = :y where x = :x\"),\n",
    "            [\n",
    "                {\"x\": 9, \"y\": 11},\n",
    "                {\"x\": 13, \"y\": 15},\n",
    "            ]\n",
    "        )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
